星尘

9. Hive-3

发表于 更新于 分类于

使用 beeline 和 hiveserver2 连接

在一个窗口中使用 hiveserver2，让窗口一直不关闭，在另一个窗口进入 beeline 后使用 JDBC 进行连接，使用 beeline 的好处是查询结果格式以更方便的形式的显示

常用函数

nvl

NVL( string1, replace_with)： 判断 string1 是否为null，如果为null，使用 replace_with 替换null，否则就返回 string1

1
select avg(nvl(comm, 0)) from emp1;

例如上述例子中，对 comm 列计算平均值，如果 comm 为 null，则使用 0 代替。如果这里不使用 nvl 函数的话，那么计算平均值是不算上 null 的个数的，数据如下所示，不使用 nvl ，平均值为 （300+500+1400+0）/ 4 = 550。使用 nvl 平均值就是 146.66666666666666

concat

字符串拼接。 可以在参数中传入多个 string 类型的字符串，但是一旦有一个参数为 null，则返回 null

1
2
select concat("a","v"); -- av
select concat("a","v",null); -- NULL

concat_ws

使用指定的分隔符完成字符串拼接，格式为 concat_ws(分隔符,[string | array<string>]+)，第一个参数为分隔符，后面为要拼接的字符串或者数组。

1
select concat_ws(".","zhansan",array("lisi","wangwu"));  -- zhansan.lisi.wangwu

collect_set

collect_set(列名) 作用是将此列的多行记录合并为一个 set 集合，去重

collect_list

collect_list(列名) 作用是将此列的多行记录合并为一个 set 集合，不去重

explode

explode(列名) 参数只能是 array 或 map, 将 array 类型参数转为1列N行, 将 map 类型参数转为2列N行

练习

练习一

有以下数据，求出不同部门男女各多少人

期望查询结果如下

1
2
A     2       1
B     1       2

方法1，按照部门分组，单独查询出男女的个数，然后在将结果进行汇总。下面方式 HQL 性能不高，有两个字查询，一个汇总，所以需要三个 job 才能运行完成。

1
2
3
4
5
select a.dept_id, male_count, female_count from
(select dept_id, count(1) male_count from emp_sex where sex="男" group by dept_id) a
join
(select dept_id, count(1) female_count from emp_sex where sex="女" group by dept_id) b
on a.dept_id = b.dept_id;

方法2，利用 sum 函数，在求男性总人数时，如果当前人的性别为男，记1，否则记 0，sum 该字段就得到了男性的值。在求女性总人数时，如果当前人的性别为女，记1，否则记0，sum 该字段就得到了男性的值。

case when 的语法如下，这一列中出现的 值1 会被替换为 值2 , 值3 会被替换为 值4, 其他的替换为值5

1
2
3
4
5
6
case  列名 
    when  值1  then  值2
    when  值3  then  值4
    ...
    else 值5
end
1
2
3
4
select dept_id,
sum(case sex when "男" then 1 else 0 end) male_count,
sum(case sex when "女" then 1 else 0 end) female_count
from emp_sex group by dept_id;

使用 case when 的优化 HQL 如上，只需要一个 job 就能完成。

HQL 中除了 case when 能做判断外，if 也能做判断，语法为 if(判断表达式,值1,值2)，值1当表达式为 true 时获取，值2当表达式为 false 时获取。实现 HQL 如下：

1
2
3
4
select dept_id,
sum(if(sex == "男", 1, 0)) male_count,
sum(if(sex == "女", 1, 0)) female_count
from emp_sex group by dept_id;

这个 HQL 也是只需要一个 job 就能完成。

练习二

把星座和血型一样的人归类到一起。结果如下：

1
2
3
射手座,A            大海|凤姐
白羊座,A            孙悟空|猪八戒
白羊座,B            宋宋

这里需要使用函数 concat_ws 进行字符串的拼接，还需要使用 collect_list 将某列的多行记录合并为一个 list 集合，不去重。

1
2
3
select  concat(constellation,',',blood_type),concat_ws('|',collect_list(name))
from person_info
group by constellation,blood_type

练习三

将电影分类中的数组数据展开。结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
《疑犯追踪》      悬疑
《疑犯追踪》      动作
《疑犯追踪》      科幻
《疑犯追踪》      剧情
《Lie to me》   悬疑
《Lie to me》   警匪
《Lie to me》   动作
《Lie to me》   心理
《Lie to me》   剧情
《战狼2》        战争
《战狼2》        动作
《战狼2》        灾难

将 category 列进行 explode，即转换为 1列N行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
0: jdbc:hive2://hadoop10:10000> select explode(category) from movie_info;
+------+--+
| col  |
+------+--+
| 悬疑   |
| 动作   |
| 科幻   |
| 剧情   |
| 悬疑   |
| 警匪   |
| 动作   |
| 心理   |
| 剧情   |
| 战争   |
| 动作   |
| 灾难   |
+------+--+

1
2
select movie,col1
from movie_info lateral view explode(category) tmp1 as col1

lateral view 的作用是将炸裂后的1列N行，在逻辑上依然视作1列1行，实际是1列N行，然后查询时和movie进行笛卡尔集这个操作在hive中称为侧写(lateral view)

练习四

有以下数据

1
2
3
person_info2.names      person_info2.tags       person_info2.hobbys
["jack","tom","jerry"]  ["阳光男孩","肌肉男孩","直男"]  ["晒太阳","健身","说多喝热水"]
["marry","nancy"]       ["阳光女孩","肌肉女孩","腐女"]  ["晒太阳","健身","看有内涵的段子"]

希望得到以下查询结果

1
2
3
4
5
6
7
8
期望结果：
jack	阳光男孩	晒太阳
jack	阳光男孩	健身
jack	阳光男孩	说多喝热水
jack	肌肉男孩	晒太阳
jack	肌肉男孩	健身
jack	肌肉男孩	说多喝热水
.....

查询 SQL 

1
2
3
4
5
select name,tag,hobby
from person_info2
lateral view explode(names) tmp1 as name
lateral view explode(tags) tmp1 as tag
lateral view explode(hobbys) tmp1 as hobby;

窗口函数

即可以在函数在运行时通过改变窗口的大小，来控制计算的数据集的范围。

文档地址 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics

常用函数

窗口函数有以下三类

第一类

1
2
3
4
5
6
7
LEAD: LEAD (scalar_expression [,offset] [,default])： 返回当前行以下N行的指定列的列值,如果找不到，就采用默认值

LAG: LAG (scalar_expression [,offset] [,default])： 返回当前行以上N行的指定列的列值,如果找不到，就采用默认值

FIRST_VALUE: FIRST_VALUE(列名,[false(默认)])：  返回当前窗口指定列的第一个值，第二个参数如果为true,代表加入第一个值为null，跳过空值，继续寻找！

LAST_VALUE: LAST_VALUE(列名,[false(默认)])：  返回当前窗口指定列的最后一个值，第二个参数如果为true,代表加入第一个值为null，跳过空值，继续寻找！

第二类

1
统计类的函数(一般都需要结合over使用)： min,max,avg,sum,count

第三类

1
排名分析函数：RANK，ROW_NUMBER，DENSE_RANK，CUME_DIST，PERCENT_RANK，NTILE

语法

相关语法解释

1
2
3
4
5
6
7
8
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化
CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
LAG(col,n)：往前第n行数据
LEAD(col,n)：往后第n行数据
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

窗口函数的使用格式为 函数 over( partition by 字段 ,order by 字段 window_clause ) window_clause 为窗口的大小，窗口大小常用格式如下

1
2
3
4
5
(rows | range) between (unbounded | [num]) preceding and ([num] preceding | current row | (unbounded | [num]) following)

(rows | range) between current row and (current row | (unbounded | [num]) following)

(rows | range) between [num] following and (unbounded | [num]) following

特殊情况：

  1. 在 over() 中既没有出现 windows_clause，也没有出现 order by，窗口默认为 rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
  2. 在over()中没有出现 windows_clause，但指定了 order by，窗口默认为 rows between UNBOUNDED PRECEDING and CURRENT ROW

窗口函数和分组有什么区别：

  1. 如果是分组操作，select 后只能写分组后的字段
  2. 如果是窗口函数，窗口函数是在指定的窗口内，对每条记录都执行一次函数
  3. 如果是分组操作，有去重效果，而窗口函数中的 partition不去重

注意⚠️：不是所有的函数在运行都是可以通过改变窗口的大小，来控制计算的数据集的范围，所有的排名函数和LAG,LEAD，支持使用 over()，但是在 over() 中不能定义 window_clause。

查询示例

例如有以下数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
0: jdbc:hive2://hadoop10:10000> select * from business;
+----------------+---------------------+----------------+--+
| business.name  | business.orderdate  | business.cost  |
+----------------+---------------------+----------------+--+
| jack           | 2017-01-01          | 10             |
| tony           | 2017-01-02          | 15             |
| jack           | 2017-02-03          | 23             |
| tony           | 2017-01-04          | 29             |
| jack           | 2017-01-05          | 46             |
| jack           | 2017-04-06          | 42             |
| tony           | 2017-01-07          | 50             |
| jack           | 2017-01-08          | 55             |
| mart           | 2017-04-08          | 62             |
| mart           | 2017-04-09          | 68             |
| neil           | 2017-05-10          | 12             |
| mart           | 2017-04-11          | 75             |
| neil           | 2017-06-12          | 80             |
| mart           | 2017-04-13          | 94             |
+----------------+---------------------+----------------+--+

需要进行以下查询

（1）查询在2017年4月份购买过的顾客及总人数

1
2
3
4
select name,count(*) over(rows between UNBOUNDED  PRECEDING and UNBOUNDED  FOLLOWING)
from business
where substring(orderdate,1,7)='2017-04'
group by name

上面的写法等价于

1
2
3
4
select name,count(*) over()
from business
where substring(orderdate,1,7)='2017-04'
group by name

查询结果为

1
2
3
4
5
6
+-------+-----------------+--+
| name  | count_window_0  |
+-------+-----------------+--+
| mart  | 2               |
| jack  | 2               |
+-------+-----------------+--+

（2）查询顾客的购买明细及月购买总额

1
2
select name,orderdate,cost,sum(cost) over(partition by name,substring(orderdate,1,7) ) 
from business

查询 name, orderdate, cost，并对 cost 进行按月份的累加求和, 结果如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
+-------+-------------+-------+---------------+--+
| name  |  orderdate  | cost  | sum_window_0  |
+-------+-------------+-------+---------------+--+
| jack  | 2017-01-05  | 46    | 111           |
| jack  | 2017-01-08  | 55    | 111           |
| jack  | 2017-01-01  | 10    | 111           |
| jack  | 2017-02-03  | 23    | 23            |
| jack  | 2017-04-06  | 42    | 42            |
| mart  | 2017-04-13  | 94    | 299           |
| mart  | 2017-04-11  | 75    | 299           |
| mart  | 2017-04-09  | 68    | 299           |
| mart  | 2017-04-08  | 62    | 299           |
| neil  | 2017-05-10  | 12    | 12            |
| neil  | 2017-06-12  | 80    | 80            |
| tony  | 2017-01-04  | 29    | 94            |
| tony  | 2017-01-02  | 15    | 94            |
| tony  | 2017-01-07  | 50    | 94            |
+-------+-------------+-------+---------------+--+

（3）查询顾客的购买明细, 并将cost按照日期进行累加

1
2
select name,orderdate,cost,sum(cost) over(partition by name order by orderdate ) 
from business

查询 name, orderdate, cost，并对 cost 进行按名称的累加，结果如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
+-------+-------------+-------+---------------+--+
| name  |  orderdate  | cost  | sum_window_0  |
+-------+-------------+-------+---------------+--+
| jack  | 2017-01-01  | 10    | 10            |
| jack  | 2017-01-05  | 46    | 56            |
| jack  | 2017-01-08  | 55    | 111           |
| jack  | 2017-02-03  | 23    | 134           |
| jack  | 2017-04-06  | 42    | 176           |
| mart  | 2017-04-08  | 62    | 62            |
| mart  | 2017-04-09  | 68    | 130           |
| mart  | 2017-04-11  | 75    | 205           |
| mart  | 2017-04-13  | 94    | 299           |
| neil  | 2017-05-10  | 12    | 12            |
| neil  | 2017-06-12  | 80    | 92            |
| tony  | 2017-01-02  | 15    | 15            |
| tony  | 2017-01-04  | 29    | 44            |
| tony  | 2017-01-07  | 50    | 94            |
+-------+-------------+-------+---------------+--+

（4）查询顾客的购买明细及顾客上次的购买时间

1
select name,orderdate,cost,lag(orderdate,1,'无数据') over(partition by name order by orderdate) from business

LAG(col,n) 表示取之前的第n行的 col 列的数据

上面查询代表查询 name,orderdate,cost 信息，并且取 orderdate 的上一条数据，如果没数据就显示 “无数据”，并按 name 分组，orderdate 排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
+-------+-------------+-------+---------------+--+
| name  |  orderdate  | cost  | lag_window_0  |
+-------+-------------+-------+---------------+--+
| jack  | 2017-01-01  | 10    | 无数据           |
| jack  | 2017-01-05  | 46    | 2017-01-01    |
| jack  | 2017-01-08  | 55    | 2017-01-05    |
| jack  | 2017-02-03  | 23    | 2017-01-08    |
| jack  | 2017-04-06  | 42    | 2017-02-03    |
| mart  | 2017-04-08  | 62    | 无数据           |
| mart  | 2017-04-09  | 68    | 2017-04-08    |
| mart  | 2017-04-11  | 75    | 2017-04-09    |
| mart  | 2017-04-13  | 94    | 2017-04-11    |
| neil  | 2017-05-10  | 12    | 无数据           |
| neil  | 2017-06-12  | 80    | 2017-05-10    |
| tony  | 2017-01-02  | 15    | 无数据           |
| tony  | 2017-01-04  | 29    | 2017-01-02    |
| tony  | 2017-01-07  | 50    | 2017-01-04    |
+-------+-------------+-------+---------------+--+

（5）查询顾客的购买明细及顾客下次的购买时间

和上面差不多，只不过需要将函数 lag 换为 lead

1
select name,orderdate,cost,lead(orderdate,1,'无数据') over(partition by name order by orderdate ) from business

结果如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
+-------+-------------+-------+----------------+--+
| name  |  orderdate  | cost  | lead_window_0  |
+-------+-------------+-------+----------------+--+
| jack  | 2017-01-01  | 10    | 2017-01-05     |
| jack  | 2017-01-05  | 46    | 2017-01-08     |
| jack  | 2017-01-08  | 55    | 2017-02-03     |
| jack  | 2017-02-03  | 23    | 2017-04-06     |
| jack  | 2017-04-06  | 42    | 无数据            |
| mart  | 2017-04-08  | 62    | 2017-04-09     |
| mart  | 2017-04-09  | 68    | 2017-04-11     |
| mart  | 2017-04-11  | 75    | 2017-04-13     |
| mart  | 2017-04-13  | 94    | 无数据            |
| neil  | 2017-05-10  | 12    | 2017-06-12     |
| neil  | 2017-06-12  | 80    | 无数据            |
| tony  | 2017-01-02  | 15    | 2017-01-04     |
| tony  | 2017-01-04  | 29    | 2017-01-07     |
| tony  | 2017-01-07  | 50    | 无数据            |
+-------+-------------+-------+----------------+--+

（6）查询顾客的购买明细及顾客本月第一次购买的时间

第一次购买的时间只需要按照购买时间拍正序，然后取第一条记录即可，这里使用函数 FIRST_VALUE 取第一条记录。

1
select name,orderdate,cost,FIRST_VALUE(orderdate,true) over(partition by name,substring(orderdate,1,7) order by orderdate ) from business

查询结果如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
+-------+-------------+-------+-----------------------+--+
| name  |  orderdate  | cost  | first_value_window_0  |
+-------+-------------+-------+-----------------------+--+
| jack  | 2017-01-01  | 10    | 2017-01-01            |
| jack  | 2017-01-05  | 46    | 2017-01-01            |
| jack  | 2017-01-08  | 55    | 2017-01-01            |
| jack  | 2017-02-03  | 23    | 2017-02-03            |
| jack  | 2017-04-06  | 42    | 2017-04-06            |
| mart  | 2017-04-08  | 62    | 2017-04-08            |
| mart  | 2017-04-09  | 68    | 2017-04-08            |
| mart  | 2017-04-11  | 75    | 2017-04-08            |
| mart  | 2017-04-13  | 94    | 2017-04-08            |
| neil  | 2017-05-10  | 12    | 2017-05-10            |
| neil  | 2017-06-12  | 80    | 2017-06-12            |
| tony  | 2017-01-02  | 15    | 2017-01-02            |
| tony  | 2017-01-04  | 29    | 2017-01-02            |
| tony  | 2017-01-07  | 50    | 2017-01-02            |
+-------+-------------+-------+-----------------------+--+

（7）查询顾客的购买明细及顾客本月最后一次购买的时间

和上面类似，只是使用函数不同, 因为是取本月最后一次购买的时间，所以这里要限制窗口的位置，窗口的位置就是当前行到本次分组的最后一行。

1
select name,orderdate,cost,LAST_VALUE(orderdate,true) over(partition by name,substring(orderdate,1,7) order by orderdate rows between CURRENT row and UNBOUNDED  FOLLOWING) from business

查询结果如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
+-------+-------------+-------+----------------------+--+
| name  |  orderdate  | cost  | last_value_window_0  |
+-------+-------------+-------+----------------------+--+
| jack  | 2017-01-01  | 10    | 2017-01-08           |
| jack  | 2017-01-05  | 46    | 2017-01-08           |
| jack  | 2017-01-08  | 55    | 2017-01-08           |
| jack  | 2017-02-03  | 23    | 2017-02-03           |
| jack  | 2017-04-06  | 42    | 2017-04-06           |
| mart  | 2017-04-08  | 62    | 2017-04-13           |
| mart  | 2017-04-09  | 68    | 2017-04-13           |
| mart  | 2017-04-11  | 75    | 2017-04-13           |
| mart  | 2017-04-13  | 94    | 2017-04-13           |
| neil  | 2017-05-10  | 12    | 2017-05-10           |
| neil  | 2017-06-12  | 80    | 2017-06-12           |
| tony  | 2017-01-02  | 15    | 2017-01-07           |
| tony  | 2017-01-04  | 29    | 2017-01-07           |
| tony  | 2017-01-07  | 50    | 2017-01-07           |
+-------+-------------+-------+----------------------+--+

（8）查询顾客的购买明细及顾客最近三次cost花费

最近三次可以是 当前和之前两次 或 当前+前一次+后一次

当前和之前两次:

1
select name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between 2 PRECEDING and CURRENT row) from business

当前+前一次+后一次:

1
select name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and 1 FOLLOWING) from business

查询结果如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
+-------+-------------+-------+---------------+--+
| name  |  orderdate  | cost  | sum_window_0  |
+-------+-------------+-------+---------------+--+
| jack  | 2017-01-01  | 10    | 56            |
| jack  | 2017-01-05  | 46    | 111           |
| jack  | 2017-01-08  | 55    | 124           |
| jack  | 2017-02-03  | 23    | 120           |
| jack  | 2017-04-06  | 42    | 65            |
| mart  | 2017-04-08  | 62    | 130           |
| mart  | 2017-04-09  | 68    | 205           |
| mart  | 2017-04-11  | 75    | 237           |
| mart  | 2017-04-13  | 94    | 169           |
| neil  | 2017-05-10  | 12    | 92            |
| neil  | 2017-06-12  | 80    | 92            |
| tony  | 2017-01-02  | 15    | 44            |
| tony  | 2017-01-04  | 29    | 94            |
| tony  | 2017-01-07  | 50    | 79            |
+-------+-------------+-------+---------------+--+

（9）查询前20%时间的订单信息

排名函数

1
2
3
4
5
6
RANK: 允许并列，一旦有并列跳号！ 
ROW_NUMBER: 行号！ 连续的，每个号之间差1！
DENSE_RANK： 允许并列，一旦有并列不跳号！
CUME_DIST：  从排序后的第一行到当前值之间数据 占整个数据集的百分比！
PERCENT_RANK：  rank-1/ 总数据量-1   
NTILE(x):  将数据集均分到X个组中，返回每条记录所在的组号

示例数据如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
+-------------+----------------+--------------+--+
| score.name  | score.subject  | score.score  |
+-------------+----------------+--------------+--+
| 孙悟空         | 语文             | 87           |
| 孙悟空         | 数学             | 95           |
| 孙悟空         | 英语             | 68           |
| 大海          | 语文             | 94           |
| 大海          | 数学             | 56           |
| 大海          | 英语             | 84           |
| 宋宋          | 语文             | 64           |
| 宋宋          | 数学             | 86           |
| 宋宋          | 英语             | 84           |
| 婷婷          | 语文             | 65           |
| 婷婷          | 数学             | 85           |
| 婷婷          | 英语             | 78           |
+-------------+----------------+--------------+--+

查询示例如下

1
2
3
4
5
6
select  *,rank() over(order by score) ranknum,
ROW_NUMBER() over(order by score) rnnum,
DENSE_RANK() over(order by score) drnum,
CUME_DIST() over(order by score) cdnum,
PERCENT_RANK() over(order by score) prnum
from score;

查询结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
score.name      score.subject   score.score     ranknum rnnum   drnum   cdnum   prnum
大海    数学    56      1       1       1       0.08333333333333333     0.0
宋宋    语文    64      2       2       2       0.16666666666666666     0.09090909090909091
婷婷    语文    65      3       3       3       0.25    0.18181818181818182
孙悟空  英语    68      4       4       4       0.3333333333333333      0.2727272727272727
婷婷    英语    78      5       5       5       0.4166666666666667      0.36363636363636365
宋宋    英语    84      6       6       6       0.5833333333333334      0.45454545454545453
大海    英语    84      6       7       6       0.5833333333333334      0.45454545454545453
婷婷    数学    85      8       8       7       0.6666666666666666      0.6363636363636364
宋宋    数学    86      9       9       8       0.75    0.7272727272727273
孙悟空  语文    87      10      10      9       0.8333333333333334      0.8181818181818182
大海    语文    94      11      11      10      0.9166666666666666      0.9090909090909091
孙悟空  数学    95      12      12      11      1.0     1.0
Time taken: 38.666 seconds, Fetched: 12 row(s)

查询示例

（1） 按照科目进行排名

1
select *,rank() over(partition by subject order by score desc) from score

查询结果如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
+-------------+----------------+--------------+----------------+--+
| score.name  | score.subject  | score.score  | rank_window_0  |
+-------------+----------------+--------------+----------------+--+
| 孙悟空         | 数学             | 95           | 1              |
| 宋宋          | 数学             | 86           | 2              |
| 婷婷          | 数学             | 85           | 3              |
| 大海          | 数学             | 56           | 4              |
| 宋宋          | 英语             | 84           | 1              |
| 大海          | 英语             | 84           | 1              |
| 婷婷          | 英语             | 78           | 3              |
| 孙悟空         | 英语             | 68           | 4              |
| 大海          | 语文             | 94           | 1              |
| 孙悟空         | 语文             | 87           | 2              |
| 婷婷          | 语文             | 65           | 3              |
| 宋宋          | 语文             | 64           | 4              |
+-------------+----------------+--------------+----------------+--+

（2）给每个学生的总分进行排名

1
2
3
4
5
select name,sumscore,rank()  over( order by sumscore desc)
from
(select name,sum(score) sumscore
from  score
group by  name) tmp
1
2
3
4
5
6
7
8
+-------+-----------+----------------+--+
| name  | sumscore  | rank_window_0  |
+-------+-----------+----------------+--+
| 孙悟空   | 250       | 1              |
| 宋宋    | 234       | 2              |
| 大海    | 234       | 2              |
| 婷婷    | 228       | 4              |
+-------+-----------+----------------+--+

（3）求每个学生的成绩明细及给每个学生的总分和总分排名

1
2
3
4
select *,DENSE_RANK() over(order by tmp.sumscore desc)
from
(select *,sum(score) over(partition by name)  sumscore
from score) tmp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
+-----------+--------------+------------+---------------+----------------------+--+
| tmp.name  | tmp.subject  | tmp.score  | tmp.sumscore  | dense_rank_window_0  |
+-----------+--------------+------------+---------------+----------------------+--+
| 孙悟空       | 语文           | 87         | 250           | 1                    |
| 孙悟空       | 数学           | 95         | 250           | 1                    |
| 孙悟空       | 英语           | 68         | 250           | 1                    |
| 宋宋        | 语文           | 64         | 234           | 2                    |
| 宋宋        | 数学           | 86         | 234           | 2                    |
| 宋宋        | 英语           | 84         | 234           | 2                    |
| 大海        | 语文           | 94         | 234           | 2                    |
| 大海        | 数学           | 56         | 234           | 2                    |
| 大海        | 英语           | 84         | 234           | 2                    |
| 婷婷        | 语文           | 65         | 228           | 3                    |
| 婷婷        | 数学           | 85         | 228           | 3                    |
| 婷婷        | 英语           | 78         | 228           | 3                    |
+-----------+--------------+------------+---------------+----------------------+--+

（4）只查询每个科目的成绩的前2名

1
2
3
4
5
select *
from
(select *,rank() over(partition by subject order by score desc) rn
from score) tmp
where rn<=2
1
2
3
4
5
6
7
8
9
10
+-----------+--------------+------------+---------+--+
| tmp.name  | tmp.subject  | tmp.score  | tmp.rn  |
+-----------+--------------+------------+---------+--+
| 孙悟空       | 数学           | 95         | 1       |
| 宋宋        | 数学           | 86         | 2       |
| 宋宋        | 英语           | 84         | 1       |
| 大海        | 英语           | 84         | 1       |
| 大海        | 语文           | 94         | 1       |
| 孙悟空       | 语文           | 87         | 2       |
+-----------+--------------+------------+---------+--+

（5）查询学生成绩明细，并显示当前科目最高分

1
2
select *,max(score) over(partition by subject)
from score

或者

1
2
select *,FIRST_VALUE(score) over(partition by subject order by score desc)
from score
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
+-------------+----------------+--------------+---------------+--+
| score.name  | score.subject  | score.score  | max_window_0  |
+-------------+----------------+--------------+---------------+--+
| 婷婷          | 数学             | 85           | 95            |
| 宋宋          | 数学             | 86           | 95            |
| 大海          | 数学             | 56           | 95            |
| 孙悟空         | 数学             | 95           | 95            |
| 婷婷          | 英语             | 78           | 84            |
| 宋宋          | 英语             | 84           | 84            |
| 大海          | 英语             | 84           | 84            |
| 孙悟空         | 英语             | 68           | 84            |
| 婷婷          | 语文             | 65           | 94            |
| 宋宋          | 语文             | 64           | 94            |
| 大海          | 语文             | 94           | 94            |
| 孙悟空         | 语文             | 87           | 94            |
+-------------+----------------+--------------+---------------+--+